package com.hotel.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hotel.entity.Room;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * 房间Mapper接口
 */
@Mapper
public interface RoomMapper extends BaseMapper<Room> {
    
    /**
     * 获取房间状态统计
     */
    @Select("SELECT status, COUNT(*) as count FROM room GROUP BY status")
    List<Map<String, Object>> getRoomStatusStats();
    
    /**
     * 获取楼层房间分布
     */
    @Select("SELECT floor, COUNT(*) as total_rooms, " +
            "SUM(CASE WHEN status = 'available' THEN 1 ELSE 0 END) as available_rooms, " +
            "SUM(CASE WHEN status = 'occupied' THEN 1 ELSE 0 END) as occupied_rooms, " +
            "SUM(CASE WHEN status = 'cleaning' THEN 1 ELSE 0 END) as cleaning_rooms, " +
            "SUM(CASE WHEN status = 'maintenance' THEN 1 ELSE 0 END) as maintenance_rooms, " +
            "SUM(CASE WHEN status = 'out_of_order' THEN 1 ELSE 0 END) as out_of_order_rooms " +
            "FROM room GROUP BY floor ORDER BY floor")
    List<Map<String, Object>> getFloorRoomDistribution();
    
    /**
     * 获取可用房间列表
     */
    @Select("SELECT r.* FROM room r " +
            "WHERE r.room_type_id = #{roomTypeId} " +
            "AND r.status = 'available' " +
            "AND r.id NOT IN (" +
            "  SELECT DISTINCT res.room_id FROM reservation res " +
            "  WHERE res.room_id IS NOT NULL " +
            "  AND res.status IN ('confirmed', 'checkedin') " +
            "  AND NOT (res.checkout_date <= #{checkinDate} OR res.checkin_date >= #{checkoutDate})" +
            ")")
    List<Room> getAvailableRooms(@Param("roomTypeId") Long roomTypeId, 
                                 @Param("checkinDate") String checkinDate, 
                                 @Param("checkoutDate") String checkoutDate);
    
    /**
     * 获取房间入住历史
     */
    @Select("SELECT res.customer_name, res.customer_phone, res.checkin_date, res.checkout_date, " +
            "res.total_amount, res.status, res.create_time " +
            "FROM reservation res " +
            "WHERE res.room_id = #{roomId} " +
            "AND res.status IN ('checkedin', 'checkedout') " +
            "ORDER BY res.checkin_date DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getRoomCheckinHistory(@Param("roomId") Long roomId, @Param("limit") int limit);
    
    /**
     * 检查房间是否可用
     */
    @Select("SELECT COUNT(*) FROM reservation " +
            "WHERE room_id = #{roomId} " +
            "AND status IN ('confirmed', 'checkedin') " +
            "AND NOT (checkout_date <= #{checkinDate} OR checkin_date >= #{checkoutDate})")
    int checkRoomAvailability(@Param("roomId") Long roomId, 
                              @Param("checkinDate") String checkinDate, 
                              @Param("checkoutDate") String checkoutDate);
    
    /**
     * 更新房间清洁时间
     */
    @Update("UPDATE room SET last_clean_time = NOW(), status = 'available' WHERE id = #{roomId}")
    int updateRoomCleanTime(@Param("roomId") Long roomId);
    
    /**
     * 更新房间维护时间
     */
    @Update("UPDATE room SET last_maintenance_time = NOW(), status = 'available' WHERE id = #{roomId}")
    int updateRoomMaintenanceTime(@Param("roomId") Long roomId);
    
    /**
     * 根据房间类型统计房间数量
     */
    @Select("SELECT rt.name as room_type_name, COUNT(r.id) as room_count " +
            "FROM room_type rt " +
            "LEFT JOIN room r ON rt.id = r.room_type_id " +
            "GROUP BY rt.id, rt.name " +
            "ORDER BY rt.sort_order, rt.name")
    List<Map<String, Object>> getRoomCountByType();
    
    /**
     * 获取房间收入统计
     */
    @Select("SELECT r.room_number, r.floor, rt.name as room_type_name, " +
            "COUNT(res.id) as total_reservations, " +
            "COALESCE(SUM(res.total_amount), 0) as total_revenue " +
            "FROM room r " +
            "LEFT JOIN room_type rt ON r.room_type_id = rt.id " +
            "LEFT JOIN reservation res ON r.id = res.room_id AND res.status = 'checkedout' " +
            "GROUP BY r.id, r.room_number, r.floor, rt.name " +
            "ORDER BY total_revenue DESC")
    List<Map<String, Object>> getRoomRevenueStats();
    
    /**
     * 获取房间入住率统计
     */
    @Select("SELECT r.room_number, r.floor, rt.name as room_type_name, " +
            "COUNT(res.id) as total_nights, " +
            "ROUND(COUNT(res.id) * 100.0 / DATEDIFF(CURDATE(), DATE_SUB(CURDATE(), INTERVAL 30 DAY)), 2) as occupancy_rate " +
            "FROM room r " +
            "LEFT JOIN room_type rt ON r.room_type_id = rt.id " +
            "LEFT JOIN reservation res ON r.id = res.room_id " +
            "  AND res.status IN ('checkedin', 'checkedout') " +
            "  AND res.checkin_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
            "GROUP BY r.id, r.room_number, r.floor, rt.name " +
            "ORDER BY occupancy_rate DESC")
    List<Map<String, Object>> getRoomOccupancyStats();
}